import os
import xlwings as xw
import pandas as pd

file_path = '../Files'
file_list = os.listdir(file_path)
app = xw.App(visible=True, add_book=False)
for file in file_list:
    if file.startswith('~$'):
        continue
    file_with_folder = os.path.join(file_path, file)
    workbook = app.books.open(file_with_folder)
    worksheet = workbook.sheets['规格表2']
    if worksheet is not None:
        values = worksheet.range('A1').options(pd.DataFrame, header=1, index=False, expand='table').value  # 读取指定工作表数据
        values['规格'] = '长宽高-' + values['Length'].astype('str') + '*' + values['Width'].astype('str') + '*' + values[
            'High'].astype('str')

        new_values = values.drop(columns=['Length'], inplace=False)
        new_values.drop(columns=['Width'], inplace=True)
        new_values.drop(columns=['High'], inplace=True)
        worksheet_new = workbook.sheets['规格表3']
        if worksheet_new is None:
            worksheet_new = workbook.sheets.add('规格表3')
        worksheet_new['A1'].clear()
        worksheet_new['A1'].options(index=False).value = new_values
        worksheet_new.autofit()
    workbook.save()
    workbook.close()
app.quit()
